Goal:

This example will demonstrate the ability of Teiid to integrate multiple datasources so that a single query can return results from all of those sources. If you are not familiar with Teiid nomenclature please take few minutes to read basics http://teiid.jboss.org/basics/

Time: 30 mins to 1hr
Skill: Beginner
Prerequisites: SQL knowledge

Quick Install of Teiid

Download the Teiid like 8.13.3 with WildFly make sure the link you are downloading link has x.x.x With WildFly as these will be pre-configured with all Teiid components. (Note: Teiid Designer tooling is not part of this download)

1. unzip the downloaded file into directory. Do NOT choose a directory with SPACES in them. So, do not install into "Program Files" on windows, choose something like "c:\dv".

2. change directory into the installed directory. Like "cd c:\dv\teiid-8.13.3". Below in the script this is represented as "<wildfly>" whenever this directory is referenced.

3. we need to create "two" different users to work with, an "admin" user and a "application" user. Admin user for any management purposes, Application user for the applications. To create user, execute following and follow the instructions

     bin\add-user.sh 

Admin User

What type of user do you wish to add? 
 a) Management User (mgmt-users.properties) 
 b) Application User (application-users.properties)
(a): a

Enter the details of the new user to add.
Using realm 'ManagementRealm' as discovered from the existing property files.
Username : admin
The username 'admin' is easy to guess
Are you sure you want to add user 'admin' yes/no? yes
Password : 
Re-enter Password : 
What groups do you want this user to belong to? (Please enter a comma separated list, or leave blank for none)[  ]: 
About to add user 'admin' for realm 'ManagementRealm'
Is this correct yes/no? yes
Is this new user going to be used for one AS process to connect to another AS process? 
yes/no? no

Application User

What type of user do you wish to add? 
 a) Management User (mgmt-users.properties) 
 b) Application User (application-users.properties)
(a): b

Enter the details of the new user to add.
Using realm 'ApplicationRealm' as discovered from the existing property files.
Username : user
Password : 
Re-enter Password : 
What groups do you want this user to belong to? (Please enter a comma separated list, or leave blank for none)[  ]: odata
About to add user 'user' for realm 'ApplicationRealm'
Is this correct yes/no? yes
Is this new user going to be used for one AS process to connect to another AS process? 
yes/no? no

Please note the "groups" for Application user needs to be "odata" for access with OData.

Details of Example:

In this example we have a customer's account information in H2 database, say and he/she owns multiple stocks in their portfolio. In order to know the portfolio value, we need the current stock price to compute the account value. However, as we know stock price is always changing and depends on market. In this example, for simplicity, we choose that the stock prices are in a CSV file. In a slightly complicated example, you could even get this information from a web service that is provided through some brokerage firm. Given account information and stock prices we should be able to expose the account value to the user, while at the same time not exposing any details inner workings, nor writing any code.

• Account Information, stored in a H2 database

• Market Data (Stock prices), stored in a CSV text file

Using the above two sources, we will create a Virtual Database (VDB) in Teiid, that will give access to data in both these data sources. Once the VDB is built you can issue a SQL query spanning both these sources as if they are single source using Teiid. You also create VIEW/TABLE in the VDB, that can encompass the logic of integrating these sources into higher level domain model that exposes the account value. Note that none of these steps are copying the data from your original source(s), the data still resides in original sources unlike in ETL (extract, Transform and Load) tools. You can think of Teiid as Logical Data Warehouse without hassle of moving the data. After you are done building this VDB, you can then access this VDB just like any other database like Oracle, MS-SQLServer, MySQL etc. using JDBC and ODBC drivers. Note that for the client applications Teiid will look like any another relational database. They do not know anything about how, where, when the data is collected and supplied to the calling application. So effectively, it provides a Data Abstraction layer.

The deployment architecture of Teiid in this example can be summarized in below image

There multiple steps in building this example. We will call this as "Portfolio" VDB. Note that a VDB is nothing but a simple XML file with metadata about the sources you are trying to integrate together. The steps can be defined as

1. Configuring the connections to the data sources.

2. Creating the VDB file

3. Start the WildFly/Teiid Server

4. Deploy the VDB file

5. Access the VDB using JDBC application. (ODBC is left as exercise to the user)

Configuring the connections to the data sources

In order to access the data, we need configure data sources in WildFly/Teiid server, and make sure that they are available for data access. Since we have two sources, we will configure each one separately

  <datasource jndi-name="java:/accounts-ds" pool-name="h2" enabled="true" use-java-context="true">
    <connection-url>jdbc:h2:mem:accounts;INIT=RUNSCRIPT FROM '<path/to>/teiidfiles/customer-schema.sql';</connection-url>
    <driver>h2</driver>
    <security>
        <user-name>sa</user-name>
        <password>sa</password>
    </security>
  </datasource>

SYMBOL,PRICE
IBM,83.46
RHT,11.84
BA, 44.58
ORCL,17.37

<resource-adapter id="fileQS">
    <module slot="main" id="org.jboss.teiid.resource-adapter.file"/>
    <connection-definitions>
        <connection-definition class-name="org.teiid.resource.adapter.file.FileManagedConnectionFactory" 
		jndi-name="java:/marketdata-file" enabled="true" use-java-context="true" pool-name="fileDS">
            <config-property name="ParentDirectory">
                ${jboss.home.dir}/teiidfiles/
            </config-property>
            <config-property name="AllowParentPaths">
                true
            </config-property>
        </connection-definition>
    </connection-definitions>
</resource-adapter>

Creating the VDB file

The below represents the VDB file, which contains two PHYSICAL models that represent the above H2 database and CSV file. Note they are associated to the VDB using by their JNDI name inside the server (JNDI names come from above configuration you did). The third model is a VIRTUAL model, so in this model you can define the logical views based on the PHYSICAL model's entities. Open you favorite text editor, and make sure that the below code fragment is saved as "portfoilo-vdb.xml"

<vdb name="Portfolio" version="1">
 
    <description>The Portfolio VDB</description>
 
    <model name="marketdata" type="PHYSICAL">
        <source name="text-connector" translator-name="file" connection-jndi-name="java:/marketdata-file"/>
    </model>
 
    <model name="accounts" type="PHYSICAL">
        <property name="importer.useFullSchemaName" value="false"/>
	<property name="importer.tableTypes" value="TABLE,VIEW"/>
        <source name="h2-connector" translator-name="h2" connection-jndi-name="java:/accounts-ds"/>
    </model>
 
    <model name="Portfolio" type="VIRTUAL">
        <metadata type="DDL"><![CDATA[
        CREATE VIEW StockPrice (
            symbol string,
            price bigdecimal
        ) AS  
          SELECT SP.symbol, SP.price
            FROM (EXEC MarketData.getTextFiles('*.txt')) AS f, 
            TEXTTABLE(f.file COLUMNS symbol string, price bigdecimal HEADER) AS SP;
         
        CREATE VIEW AccountValues (
            LastName string PRIMARY KEY,
            FirstName string,
            StockValue bigdecimal
        ) AS
          SELECT c.lastname as LastName, c.firstname as FirstName, sum((h.shares_count*sp.price)) as StockValue 
		FROM Customer c JOIN Account a on c.SSN=a.SSN 
		JOIN Holdings h on a.account_id = h.account_id 
		JOIN product p on h.product_id=p.id 
		JOIN StockPrice sp on sp.symbol = p.symbol
		WHERE a.type='Active'
		GROUP BY c.lastname, c.firstname;                 
        ]]> </metadata>
    </model>
</vdb>

1. The "vdb" element defines the virtual database that has a name of "Portfolio" with version "1"

2. A "model" element represents a schema that is being integrated. This sample defines two sources.

3. The "source" element inside the "model" element defines the "name" of the source (can be any name), name of the translator (defines the type of the source like oracle, db2, mysql, h2, file, ws, etc..) and the "connection-jndi-name" defines the source's JNDI name in the WildFly AS container.

4. Also note that inside the "model" elements, some "property" elements can be used to define how metadata should be imported from the source. For more information, check out the Reference Guide's Dynamic VDB section.

More information about VDB's XML format can be read from https://teiid.gitbooks.io/documents/content/reference/VDB_Definition.html

Start the WildFly/Teiid Server

Open a command prompt or Terminal and change directory to where the Teiid is installed and execute for Windows

cd c:\dv\teiid-8.13.3\bin
standalone.bat -c standalone-teiid.xml

for *inx execute

cd /dv/teiid-8.13.3/bin
./standalone.sh -c standalone-teiid.xml

Make sure it starts without any errors. (if you have another applications using ports 8080, 31000 you may run into issues)

Deploy the VDB file

There are many different ways to deploy the VDB file to Teiid server, however the simplest way to deploy is to copy the "portfolio-vdb.xml" to "<wildfly>/standalone/deployments" directory, and once you copy you should see a log entry like "Portfolio.1" is ACTIVE in console log or server's log file. Before proceeding make sure you have successfully deployed the VDB.

Congratulations! you have created/deployed your first VDB in Teiid.

Access the VDB using JDBC application

Now that the Virtual database is deployed,there are many different ways to access the views you created in Teiid.

• Use JDBC, if you are writing a Java based application. See details here https://teiid.gitbooks.io/documents/content/client-dev/JDBC_Support.html

• Use ODBC, if you are using C/C++/C#/Node.js type applications. See https://teiid.gitbooks.io/documents/content/client-dev/ODBC_Support.html

• Use OData, if you want to REST based services. See https://teiid.gitbooks.io/documents/content/client-dev/OData_Support.html

Here I will describe how to use SquirreL (http://squirrel-sql.sourceforge.net/) to access the VDB. SquirreL is JDBC client tool, using which you can access any database.

1. Download this tool from the link provided, and instal using directions here http://squirrel-sql.sourceforge.net/#installation

2. Download the Teiid java driver from Teiid's download page http://teiid.jboss.org/downloads/ (See JDBC Driver links)

3. Start the SquirreL, and then add a driver for Teiid. See the screen shots here http://squirrel-sql.sourceforge.net/index.php?page=screenshots
   use following settings
   Name: Teiid
   Example URL: jdbc:teiid:<vdb-name>@mms://<host>:<port>
   Website URL: http://teiid.org
   Note you need to add the JDBC driver download above in the "Extra Class Path" tab, and select "Class Name" at the bottom as "org.teiid.jdbc.TeiidDriver"

4. Click on Alias tab, and create an alias, using the driver created above. use the following settings
   Name: portfolio
   Driver: Teiid
   URL: jdbc:teiid:portfolio.1@mm://127.0.0.1:31000
   User: <user>
   Password: <password>

Test your connection, then press OK. Then right click on alias created, and choose "connect" to connect to the VDB and issue SQL queries like below and see the results.

SELECT * FROM StockPrice; // this shows the CSV to the table values
SELECT * FROM AccountValue; // this shows Portfolio Values of all customers
SELECT * FROM AccountValue WHERE LastName = 'Dragon'  // gets the account value of single customer

Access the VDB using OData

Open your browser and type the following on the address bar

http://127.0.0.1:8080/odata4/portfolio/Portfolio/AccountValues?$format=json